const mysql = require('mysql2/promise');
require('dotenv').config();

async function createTables() {
  console.log('🚀 创建数据库表...');
  
  try {
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      port: process.env.DB_PORT,
      user: process.env.DB_USERNAME,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_DATABASE,
    });

    console.log('✅ 数据库连接成功！');

    // 创建users表
    console.log('📝 创建users表...');
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL COMMENT '用户名',
        phone VARCHAR(20) NOT NULL COMMENT '手机号',
        password VARCHAR(255) NOT NULL COMMENT '密码',
        avatar VARCHAR(255) COMMENT '头像',
        status TINYINT DEFAULT 1 COMMENT '状态：1-正常，0-禁用',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        UNIQUE KEY unique_username (username),
        UNIQUE KEY unique_phone (phone),
        KEY idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'
    `);
    console.log('✅ users表创建成功！');

    // 创建gallery_images表
    console.log('📝 创建gallery_images表...');
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS gallery_images (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL COMMENT '用户ID',
        name VARCHAR(100) NOT NULL COMMENT '图片名称',
        url VARCHAR(500) NOT NULL COMMENT '图片URL',
        thumbnail VARCHAR(500) COMMENT '缩略图URL',
        size INT NOT NULL COMMENT '文件大小（字节）',
        width INT COMMENT '图片宽度',
        height INT COMMENT '图片高度',
        format VARCHAR(10) COMMENT '图片格式',
        description TEXT COMMENT '图片描述',
        upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        KEY idx_user_id (user_id),
        KEY idx_upload_time (upload_time)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='图片表'
    `);
    console.log('✅ gallery_images表创建成功！');

    // 插入测试用户
    console.log('📝 插入测试用户...');
    await connection.execute(`
      INSERT IGNORE INTO users (username, phone, password, status) VALUES
      ('admin', '13800138000', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1)
    `);
    console.log('✅ 测试用户创建成功！');

    // 验证表创建结果
    console.log('🔍 验证表创建结果...');
    const [tables] = await connection.execute('SHOW TABLES');
    console.log('📋 现有表:');
    tables.forEach(table => {
      console.log(`   - ${Object.values(table)[0]}`);
    });

    await connection.end();
    console.log('🎉 数据库表创建完成！');
    
  } catch (error) {
    console.error('❌ 创建表失败:', error.message);
    process.exit(1);
  }
}

createTables();

